# coding=utf-8

import web
from web.utils import storage

from db import connection
from define import dest_type_text

db = connection.default

class Project(object):
    """
    相关管理相关的数据层方法
    """
    @staticmethod
    def get(id):
        """获取给定旅游项目记录"""
        sets = db.select('travel_proj', where = 'id = $id', vars = locals())
        if sets:
            return sets[0]
        return None

    @staticmethod
    def update(id, name, type, dest_id):
        """修改一条旅游项目记录"""
        return db.update('travel_proj'
                , name = name, type = type, dest_id = dest_id
                , where = 'id = $id'
                , vars = locals())

    @staticmethod
    def add(name, type, dest_id):
        """添加一条旅游项目记录"""
        return db.insert('travel_proj'
                , name = name
                , type = type
                , dest_id = dest_id)

    @staticmethod
    def get_all(offset, length, where = ''):
        """
        获取所有未删除项目
        @offset as int, 读取偏移量
        @length as int, 读取记录数
        @where as string, 附加查询条件
        """
        # 获取结果集
        sets = db.query('''
                SELECT SQL_CALC_FOUND_ROWS p.*, d.name AS destination
                FROM travel_proj AS p
                    LEFT OUTER JOIN
                    travel_dest AS d
                        ON d.id = p.dest_id
                %s
                ORDER BY p.id DESC
                LIMIT $offset, $length;''' % where
                , vars = locals())
        # 获取总记录数
        totals = db.query('''SELECT FOUND_ROWS() AS totals;''')
        return storage(result = sets
                , totals = int(totals[0].totals))


class Tag(object):
    """
    标签管理相关的数据层方法
    """
    @staticmethod
    def get(id):
        """
        获取给定标签
        """
        sets = db.select('tag', where = 'id = $id', vars = locals())
        if sets:
            return sets[0]
        return None

    @staticmethod
    def update(id, name):
        """
        更新指定标签
        """
        return db.update('tag'
                , name = name
                , where = 'id = $id'
                , vars = locals())

    @staticmethod
    def add(name):
        """
        添加新标签
        """
        return db.insert('tag', name = name)

    @staticmethod
    def get_all():
        """
        获取所有标签
        """
        return db.select('tag')

class Destination(object):
    """
    旅行目的地相关的数据层方法
    """
    @staticmethod
    def update(id, name, pid, type):
        """
        更新给定目的地记录
        """
        return db.update('travel_dest'
                , name = name, parent_id = pid, type = type
                , where = 'id = $id'
                , vars = locals())

    @staticmethod
    def get(id):
        """
        获取指定目的地记录
        """
        sets = db.select('travel_dest'
                , where = 'id = $id'
                , vars = locals())
        if sets:
            return sets[0]
        return None

    @staticmethod
    def delete(id):
        """
        删除
        """
        return db.delete('travel_dest'
                , where = 'id = $id'
                , vars = locals())

    @staticmethod
    def add(name, pid, type):
        """
        添加目的地信息
        """
        return db.insert('travel_dest'
                , parent_id = pid
                , name = name
                , type = type)

    @staticmethod
    def get_with_depth():
        """
        获取带层级关系的目的地列表
        """
        sets = db.query("""
                    SELECT parent.id AS pid, parent.name AS pname, 
                        parent.type AS ptype, parent.parent_id AS ppid,
                        child.id AS cid, child.name AS cname, 
                        child.type AS ctype
                    FROM travel_dest AS parent
                        LEFT JOIN
                        travel_dest AS child
                            ON child.parent_id = parent.id;
                    """)
        if not sets:
            return None

        # 将结果集进行组织
        dests = {}
        for row in sets:
            if row.pid not in dests:
                dests[row.pid] = storage(id = row.pid
                        , name = row.pname
                        , parent_id = row.ppid
                        , parent = None
                        , type = dest_type_text(row.ptype)
                        , child = [])
            # 包含子节点
            if row.cid:
                child = storage(id = row.cid
                        , name = row.cname
                        , parent_id = row.pid
                        , parent = dests[row.pid]
                        , type = dest_type_text(row.ctype)
                        , child = [])
                # 将子节点添加到父节点实例中
                dests[row.pid].child.append(child)
                # 添加到dests集合中
                dests[row.cid] = child
        # 返回列表
        # 只返回parent_id = 0的数据
        return [node for key,node in dests.items() if node.parent_id == 0]
